﻿-- Use master database for create eProject database
USE master
GO

DROP DATABASE eProject
GO

-- Create database eProject
CREATE DATABASE eProject
GO

-- Use database eProject for create table
USE eProject
GO

-- DROP TABLE [Company]
CREATE TABLE [Company]
(
	[Id] BIGINT PRIMARY KEY IDENTITY NOT NULL,
	[Name] NVARCHAR(100) DEFAULT('') NOT NULL,
	[Address] NVARCHAR(255) DEFAULT('') NOT NULL,
	[PhoneNumber] VARCHAR(20) DEFAULT('') NOT NULL,
	[Email] VARCHAR(50) DEFAULT('') NOT NULL,
	[Discription] NTEXT NULL,
	[IsDeleted] BIT DEFAULT(0) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

INSERT INTO [Company]([Name],[Address],[PhoneNumber],[Email]) VALUES('JetStar',N'112 Hồng Hà, P.2, Q. Tân Bình, Tp.HCM','(84.8) 3 8450092','jetstarmail@gmail.com');
INSERT INTO [Company]([Name],[Address],[PhoneNumber],[Email]) VALUES('VietNameAirline',N'số 1 Ngõ 65 Vạn Bảo, P. Liễu Giai, Q. Ba Đình, Hà Nội','043.99.77.989','lienhe@vemaybaymata.com');
INSERT INTO [Company]([Name],[Address],[PhoneNumber],[Email]) VALUES('Air Mekong',N'số 11, 433 Đội Cấn, Vĩnh Phúc, Ba Đình, HN','(04). 66 718 668 ','airmekongmail@gmail.com');
INSERT INTO [Company]([Name],[Address],[PhoneNumber],[Email]) VALUES('VietjetAir',N'Tòa nhà HDBank, 32 Trần Hưng Đạo, Quận Hoàn Kiếm, Hà Nội','(84.4) 3728 1828','vietjetairmail@gmail.com');

-- DROP TABLE [SeatClass]
CREATE TABLE [SeatClass]
(
	[Id] INT PRIMARY KEY IDENTITY NOT NULL,
	[SeatName] NVARCHAR(100) UNIQUE NOT NULL,
	[Discription] NTEXT DEFAULT('') NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

INSERT INTO [SeatClass]([SeatName]) VALUES(N'Business');
INSERT INTO [SeatClass]([SeatName]) VALUES(N'First');
INSERT INTO [SeatClass]([SeatName]) VALUES(N'Club');
INSERT INTO [SeatClass]([SeatName]) VALUES(N'Smoking');
INSERT INTO [SeatClass]([SeatName]) VALUES(N'NonSmoking');

-- DROP TABLE [Planes]
CREATE TABLE [Planes]
(
	[Id] BIGINT PRIMARY KEY IDENTITY NOT NULL,
	[PlaneId] VARCHAR(20) UNIQUE NOT NULL,
	[PlaneName] NVARCHAR(100) NOT NULL,
	[CompanyId] BIGINT REFERENCES [Company]([Id]) NOT NULL,
	[IsDeleted] BIT DEFAULT(0) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

-- DROP TABLE [SeatOfPlanes]
CREATE TABLE [SeatOfPlanes]
(
	[Id] BIGINT IDENTITY NOT NULL,
	[PlaneId] BIGINT REFERENCES [Planes](Id) NOT NULL,
	[SeatClassId] INT REFERENCES [SeatClass](Id) NOT NULL,
	[TotalSeat] INT DEFAULT(0) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL,
	PRIMARY KEY ([PlaneId],[SeatClassId])
)
GO

INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('BL793','BOING747',1);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('BL805','BOING666',1);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('BL795','BOING432',1);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('P8611','BOING747',3);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('P8547','BOING112',3);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('VJ8677','BOING666',4);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('VJ8661','BOING332',4);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('VJ8669','BOING112',4);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('VN1173','BOING747',2);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('VN1139','BOING605',2);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('VN1141','BOING112',2);
INSERT INTO [Planes]([PlaneId],[PlaneName],[CompanyId]) VALUES('VN737','BOING423',2);

-- DROP TABLE [Cities]
CREATE TABLE [Cities]
(
	[Id] INT PRIMARY KEY IDENTITY NOT NULL,
	[Name] NVARCHAR(100) UNIQUE NOT NULL,
	[IsDeleted] BIT DEFAULT(0) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

INSERT INTO [Cities] ([Name]) VALUES (N'An Giang');
INSERT INTO [Cities] ([Name]) VALUES (N'Bà Rịa - Vũng Tàu');
INSERT INTO [Cities] ([Name]) VALUES (N'Bạc Liêu');
INSERT INTO [Cities] ([Name]) VALUES (N'Bắc Kạn');
INSERT INTO [Cities] ([Name]) VALUES (N'Bắc Giang');
INSERT INTO [Cities] ([Name]) VALUES (N'Bắc Ninh');
INSERT INTO [Cities] ([Name]) VALUES (N'Bến Tre');
INSERT INTO [Cities] ([Name]) VALUES (N'Bình Dương');
INSERT INTO [Cities] ([Name]) VALUES (N'Bình Định');
INSERT INTO [Cities] ([Name]) VALUES (N'Bình Phước');
INSERT INTO [Cities] ([Name]) VALUES (N'Bình Thuận');
INSERT INTO [Cities] ([Name]) VALUES (N'Cà Mau');
INSERT INTO [Cities] ([Name]) VALUES (N'Cao Bằng');
INSERT INTO [Cities] ([Name]) VALUES (N'Cần Thơ');
INSERT INTO [Cities] ([Name]) VALUES (N'Đà Nẵng');
INSERT INTO [Cities] ([Name]) VALUES (N'Đắk Lắk');
INSERT INTO [Cities] ([Name]) VALUES (N'Đắk Nông');
INSERT INTO [Cities] ([Name]) VALUES (N'Đồng Nai');
INSERT INTO [Cities] ([Name]) VALUES (N'Đồng Tháp');
INSERT INTO [Cities] ([Name]) VALUES (N'Điện Biên');
INSERT INTO [Cities] ([Name]) VALUES (N'Gia Lai');
INSERT INTO [Cities] ([Name]) VALUES (N'Hà Giang');
INSERT INTO [Cities] ([Name]) VALUES (N'Hà Nam');
INSERT INTO [Cities] ([Name]) VALUES (N'Hà Nội');
INSERT INTO [Cities] ([Name]) VALUES (N'Hà Tĩnh');
INSERT INTO [Cities] ([Name]) VALUES (N'Hải Dương');
INSERT INTO [Cities] ([Name]) VALUES (N'Hải Phòng');
INSERT INTO [Cities] ([Name]) VALUES (N'Hòa Bình');
INSERT INTO [Cities] ([Name]) VALUES (N'Hậu Giang');
INSERT INTO [Cities] ([Name]) VALUES (N'Hưng Yên');
INSERT INTO [Cities] ([Name]) VALUES (N'TP. Hồ Chí Minh');
INSERT INTO [Cities] ([Name]) VALUES (N'Khánh Hòa');
INSERT INTO [Cities] ([Name]) VALUES (N'Kiên Giang');
INSERT INTO [Cities] ([Name]) VALUES (N'Kon Tum');
INSERT INTO [Cities] ([Name]) VALUES (N'Lai Châu');
INSERT INTO [Cities] ([Name]) VALUES (N'Lào Cai');
INSERT INTO [Cities] ([Name]) VALUES (N'Lạng Sơn');
INSERT INTO [Cities] ([Name]) VALUES (N'Lâm Đồng');
INSERT INTO [Cities] ([Name]) VALUES (N'Long An');
INSERT INTO [Cities] ([Name]) VALUES (N'Nam Định');
INSERT INTO [Cities] ([Name]) VALUES (N'Nghệ An');
INSERT INTO [Cities] ([Name]) VALUES (N'Ninh Bình');
INSERT INTO [Cities] ([Name]) VALUES (N'Ninh Thuận');
INSERT INTO [Cities] ([Name]) VALUES (N'Phú Thọ');
INSERT INTO [Cities] ([Name]) VALUES (N'Phú Yên');
INSERT INTO [Cities] ([Name]) VALUES (N'Quảng Bình');
INSERT INTO [Cities] ([Name]) VALUES (N'Quảng Nam');
INSERT INTO [Cities] ([Name]) VALUES (N'Quảng Ngãi');
INSERT INTO [Cities] ([Name]) VALUES (N'Quảng Ninh');
INSERT INTO [Cities] ([Name]) VALUES (N'Quảng Trị');
INSERT INTO [Cities] ([Name]) VALUES (N'Sóc Trăng');
INSERT INTO [Cities] ([Name]) VALUES (N'Sơn La');
INSERT INTO [Cities] ([Name]) VALUES (N'Tây Ninh');
INSERT INTO [Cities] ([Name]) VALUES (N'Thái Bình');
INSERT INTO [Cities] ([Name]) VALUES (N'Thái Nguyên');
INSERT INTO [Cities] ([Name]) VALUES (N'Thanh Hóa');
INSERT INTO [Cities] ([Name]) VALUES (N'Thừa Thiên - Huế');
INSERT INTO [Cities] ([Name]) VALUES (N'Tiền Giang');
INSERT INTO [Cities] ([Name]) VALUES (N'Trà Vinh');
INSERT INTO [Cities] ([Name]) VALUES (N'Tuyên Quang');
INSERT INTO [Cities] ([Name]) VALUES (N'Vĩnh Long');
INSERT INTO [Cities] ([Name]) VALUES (N'Vĩnh Phúc');
INSERT INTO [Cities] ([Name]) VALUES (N'Yên Bái');

-- DROP TABLE [AirPorts]
CREATE TABLE [AirPorts]
(
	[Id] BIGINT PRIMARY KEY IDENTITY NOT NULL,
	[AirPortId] VARCHAR(20) UNIQUE NOT NULL,
	[Name] NVARCHAR(100) DEFAULT('') NOT NULL,
	[CityId] INT REFERENCES [Cities]([Id]) NOT NULL,
	[IsDeleted] BIT DEFAULT(0) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVCS/VCS', N'Sân bay Côn Đảo', 2);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVPC/UIH', N'Sân bay Phù Cát', 9);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVCM/CAH', N'Sân bay Cà Mau', 12);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVCT/VCA', N'Sân bay quốc tế Cần Thơ', 14);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVBM/BMV', N'Sân bay Buôn Ma Thuột', 16);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVDN/DAD', N'Sân bay quốc tế Đà Nẵng', 15);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVDB/DIN', N'Sân bay Điện Biên Phủ', 20);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVPK/PXU', N'Sân bay Pleiku', 21);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVCI/HPH', N'Sân bay Cát Bi', 27);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVVV/HAN', N'Sân bay quốc tế Nội Bài', 24);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVTS/SGN', N'Sân bay quốc tế Tân Sơn Nhất', 31);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVCR/CXR', N'Sân bay quốc tế Cam Ranh', 32);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVRG/VKG', N'Sân bay Rạch Giá', 33);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVPQ/PQC', N'Sân bay quốc tế Phú Quốc', 33);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVDL/DLI', N'Sân bay quốc tế Liên Khương', 38);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVVH/VII', N'Sân bay Vinh', 41);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('TBB', N'Sân bay Tuy Hòa', 45);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VDH', N'Sân bay Đồng Hới', 46);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVCA/VCL', N'Sân bay quốc tế Chu Lai', 47);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVNS/SQH', N'Sân bay Nà Sản', 52);
INSERT INTO [AirPorts]([AirPortId], [Name], [CityId]) VALUES('VVPB/HUI', N'Sân bay quốc tế Phú Bài', 57);

-- DROP TABLE [Routes]
CREATE TABLE [Routes]
(
	[Id] BIGINT PRIMARY KEY IDENTITY NOT NULL,
	[PlaneId] BIGINT REFERENCES [Planes]([Id]) NOT NULL,
	[From] BIGINT REFERENCES [AirPorts]([Id]) NOT NULL,
	[To] BIGINT REFERENCES [AirPorts]([Id]) NOT NULL,
	[Distance] BIGINT DEFAULT(0) NULL,
	[IsDeleted] BIT DEFAULT(0) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(1,10,11);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(1,5,14);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(1,2,21);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(2,1,5);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(2,5,14);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(3,4,6);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(3,6,8);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(4,12,20);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(4,10,19);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(4,19,21);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(5,7,4);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(5,8,9);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(5,3,11);
INSERT INTO [Routes]([PlaneID],[From],[To]) VALUES(5,11,17);

-- DROP TABLE [Schedules]
CREATE TABLE [Schedules]
(
	[Id] BIGINT PRIMARY KEY IDENTITY NOT NULL,
	[RouteId] BIGINT REFERENCES [Routes]([Id]) NOT NULL,
	[EffectDate] Date DEFAULT(CONVERT(Date,GETDATE())) NOT NULL CHECK ([EffectDate] >= CONVERT(Date,GETDATE())),
	[Available] BIT DEFAULT(0) NOT NULL,
	[DepartureTime] VARCHAR(20) NOT NULL,
	[ArrivalTime] VARCHAR(20) NOT NULL,
	[ScheduleType] VARCHAR(200) NOT NULL,
	[IsDeleted] BIT DEFAULT(0) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

 -- [ScheduleType] = One : 1 lần
 -- [ScheduleType] = EveryDay : Hàng ngày
 -- [ScheduleType] != EveryDay OR One (Example: Monday,Sunday....) : Ngày trong tuần


INSERT INTO [Schedules]([RouteId],[EffectDate],[DepartureTime],[ArrivalTime],[ScheduleType]) VALUES(15,'8/20/2012','8/28/2012','8/30/2012','One');
INSERT INTO [Schedules]([RouteId],[EffectDate],[DepartureTime],[ArrivalTime],[ScheduleType]) VALUES(16,'8/21/2012','9/1/2012','9/3/2012','EveryDay');
INSERT INTO [Schedules]([RouteId],[EffectDate],[DepartureTime],[ArrivalTime],[ScheduleType]) VALUES(17,'8/22/2012','9/2/2012','9/4/2012','EveryDay');
INSERT INTO [Schedules]([RouteId],[EffectDate],[DepartureTime],[ArrivalTime],[ScheduleType]) VALUES(18,'8/23/2012','9/3/2012','9/5/2012','One');
INSERT INTO [Schedules]([RouteId],[EffectDate],[DepartureTime],[ArrivalTime],[ScheduleType]) VALUES(19,'8/24/2012','9/4/2012','9/6/2012','Monday');
INSERT INTO [Schedules]([RouteId],[EffectDate],[DepartureTime],[ArrivalTime],[ScheduleType]) VALUES(20,'8/25/2012','9/5/2012','9/7/2012','Sunday');

-- DROP TABLE [ScheduleTicketPrice]
CREATE TABLE [ScheduleTicketPrice]
(
	[Id] BIGINT IDENTITY NOT NULL,
	[ScheduleId] BIGINT REFERENCES [Schedules]([Id]) NOT NULL,
	[SeatClassId] INT REFERENCES [SeatClass]([Id]) NOT NULL,
	[Price] MONEY DEFAULT(0) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL,
	PRIMARY KEY ([ScheduleId], [SeatClassId])
)
GO

-- DROP TABLE [SeatAvailable]
CREATE TABLE [SeatAvailable]
(
	[Id] BIGINT IDENTITY NOT NULL,
	[ScheduleId] BIGINT REFERENCES [Schedules]([Id]) NOT NULL,
	[DepartureDate] DATE NOT NULL,
	[SeatClassId] INT REFERENCES [SeatClass]([Id]) NOT NULL,
	[TotalSeat] INT NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL,
	PRIMARY KEY ([ScheduleId], [DepartureDate], [SeatClassId])
)
GO

-- Create table Roles for User
-- DROP TABLE [Permissions]
CREATE TABLE [Permissions]
(
	[Id] INT PRIMARY KEY IDENTITY NOT NULL,
	[PermissionName] NVARCHAR(100) NOT NULL,
	[Discription] NVARCHAR(255) NULL DEFAULT(''),
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

INSERT INTO [Permissions]([PermissionName], [Discription]) VALUES(N'IsUserManagement', N'Cho phép quản lý Users');

-- DROP TABLE [Roles]
CREATE TABLE [Roles]
(
	[Id] INT PRIMARY KEY IDENTITY NOT NULL,
	[RoleName] VARCHAR(50) NOT NULL,
	[Discription] NVARCHAR(255) NULL DEFAULT(''),
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

INSERT INTO [Roles] ([RoleName], [Discription]) VALUES ('Administrator', N'Quản trị hệ thống');
INSERT INTO [Roles] ([RoleName], [Discription]) VALUES ('Super Moderator', N'Điều hành cấp cao');
INSERT INTO [Roles] ([RoleName], [Discription]) VALUES ('Moderator', N'Điều hành viên');
INSERT INTO [Roles] ([RoleName], [Discription]) VALUES ('Registered', N'Thành viên');

-- Create table GroupRoles for UserGroups
-- DROP TABLE [RolePermissions]
CREATE TABLE [RolePermissions]
(
	[Id] INT IDENTITY NOT NULL,
	[RoleId] INT REFERENCES [Roles]([Id]) NOT NULL,
	[PermissionId] INT REFERENCES [Permissions]([Id]) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
	PRIMARY KEY ([RoleId], [PermissionId])
)
GO

-- Create table Users
-- DROP TABLE [Users]
CREATE TABLE [Users](
	[Id] BIGINT PRIMARY KEY IDENTITY NOT NULL,
	[UserName] VARCHAR(50) UNIQUE NOT NULL,
	[Password] VARCHAR(50) NOT NULL,
	[ChallengeCode] VARCHAR(20) NOT NULL,
	[Email] VARCHAR(50) NULL DEFAULT(''),
	[Roles] INT NOT NULL REFERENCES [Roles]([Id]),
	[FirstName] NVARCHAR(100) NULL DEFAULT(''),
	[LastName] NVARCHAR(100) NULL DEFAULT(''),
	[DateOfBirth] DATE NULL DEFAULT('0000-00-00'),
	[Gender] BIT NOT NULL DEFAULT(0),
	[Address] NVARCHAR(255) NULL DEFAULT(''),
	[PhoneNumber] VARCHAR(20) NULL DEFAULT(''),
	[Distance] BIGINT NULL DEFAULT(0),
	[IsApproved] BIT NOT NULL DEFAULT(0),
	[IsBlocked] BIT NOT NULL DEFAULT(0),
	[IsDeleted] BIT NOT NULL DEFAULT(0),
	[ActivationCode] UNIQUEIDENTIFIER UNIQUE NOT NULL,
	[ActivationTime] DATETIME NULL DEFAULT(GETDATE()),
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

alter table Users
	alter column ChallengeCode VARCHAR(20) NOT NULL

-- DROP TABLE [OrderDetails]
CREATE TABLE [OrderDetails]
(
	[Id] BIGINT PRIMARY KEY IDENTITY NOT NULL,
	[UserId] BIGINT REFERENCES [Users]([Id]) NOT NULL,
	[ScheduleId] BIGINT REFERENCES [Schedules]([Id]) NOT NULL,
	[SeatClassId] INT REFERENCES [SeatClass]([Id]) NOT NULL,
	[TicketPrice] MONEY DEFAULT(0) NOT NULL,
	[TotalTicket] INT DEFAULT(0) NOT NULL,
	[TotalPrice] MONEY DEFAULT(0) NOT NULL,
	[OrderDate] DATETIME DEFAULT(GETDATE()) NOT NULL,
	[Status] SMALLINT DEFAULT(0) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

-- [Status] = 0: Blocking
-- [Status] = 1: Canceling
-- [Status] = 2: Confirm

-- DROP TABLE [OrderUpdateInfo]
CREATE TABLE [OrderUpdateInfo]
(
	[Id] BIGINT PRIMARY KEY IDENTITY NOT NULL,
	[OrderId] BIGINT REFERENCES [OrderDetails]([Id]) NOT NULL,
	[Status] NVARCHAR(20) NOT NULL,
	[Comment] NVARCHAR(MAX) NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO

-- Create table Configs to configs website
-- DROP TABLE [Configs]
CREATE TABLE [Configs]
(
	[Id] BIGINT PRIMARY KEY IDENTITY NOT NULL,
	[Keyword] NVARCHAR(50) UNIQUE NOT NULL,
	[Data] NTEXT NOT NULL,
	[CreatedDate] DATETIME DEFAULT(GETDATE()) NULL,
	[LastUpdate] DATETIME DEFAULT(GETDATE()) NULL
)
GO
